﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace DynamicForm.Manager
{
    public class Record
    {
        public static void Create(string tableName, Dictionary<string, string> contextData)
        {
            string insertQuery = GetSqlInsertStatement(tableName, contextData);
            DataAccess.ExecuteNonQuery(insertQuery);
        }

        public static string GetSqlSelectStatement(string tableName, CardPageFieldsField[] fields, string whereCondition)
        {
            string query = "SELECT {0} FROM [{1}] WHERE {2}";
            string fieldList = "";
            foreach (CardPageFieldsField field in fields)
            {
                fieldList += string.Format("[{0}],", field.Name);
            }

            fieldList = fieldList.Remove(fieldList.LastIndexOf(','), 1);

            return string.Format(query, fieldList, tableName, whereCondition);
        }

        public static string GetSqlSelectStatement(string tableName, ListPage listPage)
        {
            string query = "SELECT {0} {1} FROM [{2}]";
            string topList = "";
            if (listPage.SearchCondition.TopList > 0)
            {
                topList = string.Format("TOP {0}", listPage.SearchCondition.TopList);
            }

            string fieldList = "";
            foreach (ListPageField field in listPage.Fields)
            {
                fieldList += string.Format("[{0}],", field.Name);
            }

            fieldList = fieldList.Remove(fieldList.LastIndexOf(','), 1);

            query = string.Format(query, topList, fieldList, tableName);

            if (!string.IsNullOrWhiteSpace(listPage.SearchCondition.Filter))
            {
                query = string.Format("{0} WHERE {1}", query, listPage.SearchCondition.Filter);
            }

            if (!string.IsNullOrWhiteSpace(listPage.SearchCondition.OrderBy))
            {
                query = string.Format("{0} ORDER BY {1}", query, listPage.SearchCondition.OrderBy);
            }

            return query;
        }

        public static string GetSqlInsertStatement(string tableName, Dictionary<string, string> fields)
        {
            string query = "INSERT INTO [{0}]({1}) VALUES({2})";
            string fieldList = "";
            string valueList = "";
            foreach (KeyValuePair<string, string> field in fields)
            {
                fieldList += string.Format("[{0}],", field.Key);
                valueList += string.Format("'{0}',", field.Value);
            }

            fieldList = fieldList.Remove(fieldList.LastIndexOf(','), 1);
            valueList = valueList.Remove(valueList.LastIndexOf(','), 1);

            return string.Format(query, tableName, fieldList, valueList);
        }

        public static string GetSqlUpdateStatement(string tableName, Dictionary<string, string> fields, string whereCondition)
        {
            string query = "UPDATE [{0}] SET {1} WHERE {2}";
            string fieldList = "";
            foreach (KeyValuePair<string, string> field in fields)
            {
                fieldList += string.Format("[{0}]='{1}',", field.Key, field.Value);
            }

            fieldList = fieldList.Remove(fieldList.LastIndexOf(','), 1);

            return string.Format(query, tableName, fieldList, whereCondition);
        }
    }
}